---
title: Advanced API
description: Advanced API for handling Excel files
---

FastExcel provides a set of simple and easy-to-use APIs for handling Excel files, supporting advanced features such as reading, writing, interceptors, custom styles, etc., allowing developers to flexibly handle various complex business scenarios.

# Core Concepts
If you need to perform a large amount of detailed reading and writing operations on Excel, you need to know some important concepts and classes in FastExcel. These important concepts and classes in FastExcel can provide rich options when you try to customize operations.
- `FastExcel`: Entry class used to start various operations
- `ExcelReaderBuilder`: Constructs a `ReadWorkbook` or `WriteWorkbook`, which can be understood as an Excel object; only one Excel needs to be constructed
- `ExcelReaderSheetBuilder`: Constructs a `ReadSheet` or `WriteSheet` object, which can be understood as a page in Excel; each page needs to be constructed
- `ReadListener`: Called to handle data after each row is read
- `WriteHandler`: Called to handle data for each operation, including creating cells, creating tables, etc.

All configurations are inherited. The configuration of `Workbook` will be inherited by `Sheet`, so when setting parameters in FastExcel, the scope is the entire sheet before the `FastExcel...sheet()` method, and after that, it is specific to the individual sheet.

# Entity Class Annotations
Entity classes are the foundation of read and write operations. FastExcel provides various annotations to help developers easily define fields and formats.
## **`@ExcelProperty`**
Defines the column name in Excel and the field name to map. Specific parameters are as follows:

| Name                  | Default Value       | Description                                                                                   |
|-----------------------|---------------------|-----------------------------------------------------------------------------------------------|
| value                 | Empty               | Used to match the header in Excel, must be fully matched. If there are multiple header rows, it will match the last row header. |
| order                 | Integer.MAX_VALUE   | Higher priority than `value`, will match the order of entities and data in Excel according to the order of `order`. |
| index                 | -1                  | Higher priority than `value` and `order`, will directly specify which column in Excel to match based on `index`. |
| converter             | Automatically selected | Specifies which converter the current field uses. By default, it will be automatically selected. For reading, as long as the `cn.idev.excel.converters.Converter#convertToJavaData(com.idev.excel.converters.ReadConverterContext<?>)` method is implemented, it is sufficient.

## **`@ColumnWidth`**
Specifies the column width.

## **`@DateTimeFormat`**
Date conversion, using `String` to receive data in Excel date format will trigger this annotation. Parameters are as follows:

| Name                  | Default Value  | Description                                                             |
|-----------------------|---------------|------------------------------------------------------------------------|
| value                 | Empty         | Write in reference to `java.text.SimpleDateFormat`.                    |
| use1904windowing      | Automatically selected | In Excel, time is stored as a double-precision floating-point number starting from 1900, but sometimes the default start date is 1904. So setting this value changes the default start date to 1904.

## **`@NumberFormat`**
Number conversion, using `String` to receive data in Excel number format will trigger this annotation.

| Name                  | Default Value  | Description                                                             |
|-----------------------|---------------|------------------------------------------------------------------------|
| value                 | Empty         | Write in reference to `java.text.DecimalFormat`.                      |
| roundingMode          | RoundingMode.HALF_UP | Set the rounding mode when formatting.

# Reading Operations

Both `ReadWorkbook` and `ReadSheet` have parameters that default to their parent.
| Name                  | Default Value   | Description                                                        |
|-----------------------|-----------------|--------------------------------------------------------------------|
| converter             | Empty           | Default loads many converters, here you can add unsupported fields. |
| readListener          | Empty           | Can register multiple listeners. When reading Excel, the listener's methods will be continuously called. |
| headRowNumber         | 1               | The number of rows in the header of Excel, default is 1 row.       |
| head                  | Empty           | Choose one of `head` or `clazz`. Reads the list corresponding to the file header and matches the data based on the list. It is recommended to use class. |
| clazz                 | Empty           | Choose one of `head` or `clazz`. Reads the class corresponding to the file header, and annotations can also be used. If neither is specified, all data will be read. |
| autoTrim              | true            | Automatically trims the header, reads data, etc.                   |
| use1904windowing      | false           | In Excel, time is stored as a double-precision floating-point number starting from 1900, but sometimes the default start date is 1904. So setting this value changes the default start date to 1904. |
| useScientificFormat   | false           | When converting numbers to text, whether to use scientific notation for large values. |

## ReadWorkbook (understood as an Excel object) parameters
| Name                  | Default Value   | Description                                                                                                                |
|-----------------------|-----------------|---------------------------------------------------------------------------------------------------------------------------|
| excelType             | Empty           | The current type of Excel, supports XLS, XLSX, CSV.                                                                        |
| inputStream           | Empty           | Choose between `file` and `inputStream`. Reads the file stream. If a stream is received, it is used directly. If not, it is recommended to use the `file` parameter. Using `inputStream` will help create temporary files, but in the end it is still `file`. |
| file                  | Empty           | Choose between `inputStream` and `file`. Reads the file.                                                                   |
| mandatoryUseInputStream | false          | Forces the use of `inputStream` to create objects, which may degrade performance but will not create temporary files. |
| charset               | Charset#defaultCharset | Only useful for CSV files, specifies the encoding used when reading the file.                                             |
| autoCloseStream       | true            | Automatically closes the read stream.                                                                                      |
| readCache             | Empty           | For files smaller than 5MB, use memory. For files larger than 5MB, use `EhCache`. It is not recommended to use this parameter. |
| readCacheSelector     | SimpleReadCacheSelector | Used to select when to use memory to store temporary data and when to use disk to store temporary data.                |
| ignoreEmptyRow        | true            | Ignore empty rows.                                                                                                         |
| password              | Empty           | Password for reading the file.                                                                                             |
| xlsxSAXParserFactoryName | Empty         | Specifies the name of the class used for sax reading, for example: `com.sun.org.apache.xerces.internal.jaxp.SAXParserFactoryImpl`. |
| useDefaultListener    | true            | `@since 2.1.4` <br/>By default, `ModelBuildEventListener` is added to help convert to the object passed in. Setting it to `false` will not assist in converting objects, and custom listeners will receive a `Map<Integer, CellData>` object. If you still want to receive `class` objects, call the `readListener` method and add custom `beforeListener`, `ModelBuildEventListener`, and custom `afterListener`. |
| extraReadSet          | Empty           | Set of additional content to be read, which is not read by default.                                                         |
| readDefaultReturn     | STRING          | `@since 3.2.0`<br/>STRING: Returns an array of `Map<Integer, String>`, the return value is the content you see in the Excel cell without clicking on it.<br/>ACTUAL_DATA: Returns an array of `Map<Integer, Object>`, the actual data stored, will automatically convert types, `Object` type can be `BigDecimal`, `Boolean`, `String`, `LocalDateTime`, `null`, one of them.<br/>READ_CELL_DATA: Returns an array of `Map<Integer, ReadCellData<?>>`, where `?` type refers to ACTUAL_DATA.

## ReadSheet (just a Sheet in Excel) parameters
| Name                  | Default Value   | Description                                                                 |
|-----------------------|-----------------|----------------------------------------------------------------------------|
| sheetNo               | 0               | The code of the Sheet to be read, recommended to use this to specify which Sheet to read. |
| sheetName             | Empty           | Match the Sheet by name.                                                   |

# Writing Operations

## Common Parameters
`WriteWorkbook`, `WriteSheet`, and `WriteTable` all have parameters that default to their parent.
| Name                  | Default Value   | Description                                                                 |
|-----------------------|-----------------|----------------------------------------------------------------------------|
| converter             | Empty           | Default loads many converters, here you can add unsupported fields.        |
| writeHandler          | Empty           | Writing handler. Can implement `WorkbookWriteHandler`, `SheetWriteHandler`, `RowWriteHandler`, `CellWriteHandler`, which will be called at different stages of writing to Excel. |
| relativeHeadRowIndex  | 0               | Number of rows to leave blank above Excel.                                 |
| head                  | Empty           | Choose one of `head` or `clazz`. Reads the list corresponding to the file header and matches the data based on the list. It is recommended to use class. |
| clazz                 | Empty           | Choose one of `head` or `clazz`. Reads the class corresponding to the file header, and annotations can also be used. If neither is specified, all data will be read. |
| autoTrim              | true            | Automatically trims the header, reads data, etc.                           |
| use1904windowing      | false           | In Excel, time is stored as a double-precision floating-point number starting from 1900, but sometimes the default start date is 1904. So setting this value changes the default start date to 1904. |
| useScientificFormat   | false           | When converting numbers to text, whether to use scientific notation for large values. |
| needHead              | true            | Whether to write the header to Excel.                                      |
| useDefaultStyle       | true            | Whether to use default styles.                                             |
| automaticMergeHead    | true            | Automatically merge headers, matching the same fields above, below, left, and right in the header. |
| excludeColumnIndexes  | Empty           | Exclude indexes of data in the object.                                     |
| excludeColumnFieldNames | Empty         | Exclude fields of data in the object.                                      |
| includeColumnIndexes  | Empty           | Only export indexes of data in the object.                                 |
| includeColumnFieldNames | Empty         | Only export fields of data in the object.                                  |
| orderByIncludeColumn  | false           | @since 3.3.0 When using the parameters `includeColumnFieldNames` or `includeColumnIndexes`, it will sort according to the order of the collection passed in. |
| filedCacheLocation    | THREAD_LOCAL    | @since 3.3.0 Parsing the fields of the class will have a cache. Before, it was placed globally in a Map. After 3.3.0, it is placed in ThreadLocal by default, meaning that each read and write will reparse the class. You can reflectively modify the annotations of the class, and concurrent scenarios will not affect each other.<br/>THREAD_LOCAL: Default, the cache will be cleared each time, but not the same time.<br/>MEMORY: Placed in global memory, theoretically better performance, but cannot modify the exported object through reflection, exclusion, etc.<br/>NONE: No caching, performance will degrade, consider using it when reading and writing at the same time, and you need to reflectively exclude, modify objects, etc.

## WriteWorkbook (understood as an Excel object) parameters
| Name                  | Default Value   | Description                                                                 |
|-----------------------|-----------------|----------------------------------------------------------------------------|
| excelType             | Empty           | The current type of Excel, supports XLS, XLSX, CSV.                        |
| outputStream          | Empty           | Choose between `file` and `outputStream`. Writes the file stream.          |
| file                  | Empty           | Choose between `outputStream` and `file`. Writes the file.                |
| templateInputStream   | Empty           | Template file stream.                                                      |
| templateFile          | Empty           | Template file.                                                             |
| charset               | Charset#defaultCharset | Only useful for CSV files, specifies the encoding used when writing the file. |
| autoCloseStream       | true            | Automatically closes the write stream.                                      |
| password              | Empty           | Password for reading the file.                                              |
| inMemory              | false           | Whether to process in memory, by default, a temporary file will be generated to save memory. Memory mode is more efficient, but prone to OOM. |
| writeExcelOnException | false           | If an exception occurs during writing, whether to try to write the data to Excel. |

## WriteSheet (just a Sheet in Excel) parameters
| Name | Default Value | Description |
|-----------------------|-----|---------------------------------|
| sheetNo | 0 | Code of the Sheet to be written. |
| sheetName | Empty | Name of the Sheet to be written, defaults to `sheetNo`. |

## WriteTable (a block in Excel, a table) parameters
| Name                  | Default Value   | Description                                                                 |
|-----------------------|-----------------|----------------------------------------------------------------------------|
| tableNo               | 0               | Code of the table to be written.                                           |

# **WriteHandler**

**`WriteHandler`** is an interface provided by FastExcel for intercepting the writing process when writing to an Excel file, allowing developers to customize operations such as setting cell styles, merging cells, adding hyperlinks, inserting comments, etc. By implementing `WriteHandler`, developers can have precise control over the writing process to meet complex business requirements.

---

## **WriteHandler Interface Categories**

FastExcel provides the following WriteHandler interfaces for handling different writing scenarios:

| Interface Name         | Description                                                             |
|------------------------|-------------------------------------------------------------------------|
| **CellWriteHandler**    | Interceptor at the cell level, allows custom operations on cell data and styles. |
| **RowWriteHandler**     | Row-level interceptor, used to perform additional operations after row data is written. |
| **SheetWriteHandler**   | Worksheet-level interceptor, used to set worksheet-level properties (such as freeze panes, drop-down lists, etc.). |

---

## **Implementing WriteHandler Steps**

1. **Implement the corresponding `WriteHandler` interface**:
   - Choose the appropriate interface (`CellWriteHandler`, `RowWriteHandler`, or `SheetWriteHandler`).
   - Implement the methods in the interface, defining custom logic in the methods.

2. **Register the WriteHandler**:
   - Register your custom WriteHandler when calling `FastExcel.write()` using `.registerWriteHandler()`.

---

## **CellWriteHandler Example: Setting Cell Styles**

### Function: Set the background color to yellow and font color to blue for all content cells.
```java
@Slf4j
public class CustomCellStyleHandler implements CellWriteHandler {

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        // Ensure only content cells (not headers) are operated on
        if (BooleanUtils.isNotTrue(context.getHead())) {
            WriteCellData<?> cellData = context.getFirstCellData();
            WriteCellStyle style = cellData.getOrCreateStyle();

            // Set background color to yellow
            style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            style.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

            // Set font color to blue
            WriteFont font = new WriteFont();
            font.setColor(IndexedColors.BLUE.getIndex());
            style.setWriteFont(font);

            log.info("Style set: Row {}, Column {}", context.getRowIndex(), context.getColumnIndex());
        }
    }
}
```

### Register and Use
```java
@Test
public void customCellStyleWrite() {
    String fileName = "customCellStyleWrite.xlsx";

    FastExcel.write(fileName, DemoData.class)
        .registerWriteHandler(new CustomCellStyleHandler())
        .sheet("Custom Style")
        .doWrite(data());
}
```

---

## **RowWriteHandler Example: Inserting Comments**

### Function: Insert a comment for the first row, second column of the header.
```java
@Slf4j
public class CommentRowWriteHandler implements RowWriteHandler {

    @Override
    public void afterRowDispose(RowWriteHandlerContext context) {
        if (BooleanUtils.isTrue(context.getHead())) {
            Sheet sheet = context.getWriteSheetHolder().getSheet();
            Drawing<?> drawing = sheet.createDrawingPatriarch();

            // Create a comment
            Comment comment = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 1, 0, (short) 2, 1));
            comment.setString(new XSSFRichTextString("This is a comment"));
            sheet.getRow(0).getCell(1).setCellComment(comment);

            log.info("Comment inserted at first row, second column");
        }
    }
}
```

### Register and Use
```java
@Test
public void commentWrite() {
    String fileName = "commentWrite.xlsx";

    FastExcel.write(fileName, DemoData.class)
        .registerWriteHandler(new CommentRowWriteHandler())
        .sheet("Insert Comment")
        .doWrite(data());
}
```

---

## **SheetWriteHandler Example: Adding Dropdown Lists**

### Function: Add a dropdown list for the first column of the first two rows.
```java
@Slf4j
public class DropdownSheetWriteHandler implements SheetWriteHandler {

    @Override
    public void afterSheetCreate(SheetWriteHandlerContext context) {
        Sheet sheet = context.getWriteSheetHolder().getSheet();

        // Create dropdown list range
        CellRangeAddressList range = new CellRangeAddressList(1, 2, 0, 0);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"Option 1", "Option 2"});
        DataValidation validation = helper.createValidation(constraint, range);
        sheet.addValidationData(validation);

        log.info("Dropdown list added to the first column of the first two rows");
    }
}
```

### Register and Use
```java
@Test
public void dropdownWrite() {
    String fileName = "dropdownWrite.xlsx";

    FastExcel.write(fileName, DemoData.class)
        .registerWriteHandler(new DropdownSheetWriteHandler())
        .sheet("Add Dropdown")
        .doWrite(data());
}
```

---

## **Summary**

| WriteHandler Type    | Use Case                          | Example                     |
|----------------------|-----------------------------------|-----------------------------|
| **CellWriteHandler** | Customizing cell styles, content, merging, etc. | Setting background color, font color, etc. |
| **RowWriteHandler**  | Inserting comments, row-level styles | Inserting comments          |
| **SheetWriteHandler**| Adding dropdown lists, freeze panes | Adding dropdown lists, setting freeze panes |

# **ReadListener**

**`ReadListener`** is an interface provided by FastExcel for processing each row of data when reading an Excel file. It is one of the core components of FastExcel, allowing developers to implement custom logic to handle data rows, process headers, and even perform specific operations after reading is complete.

---

##